import pandas as pd
from sqlalchemy import create_engine

# 1. 设备列表
device_list = [
    'GW42B3'
]
placeholders = ','.join([f"'{device}'" for device in device_list])

# 2. 连接数据库
engine = create_engine("mysql+pymysql://tester:tester1234@192.168.3.28:3306/gwza231dev?charset=utf8mb4")

# 3. 读取数据
query = f"""
SELECT  device_id,
        update_time AS time,
        JSON_UNQUOTE(JSON_EXTRACT(memo, '$.pa')) AS pa
FROM    map_device_his
WHERE   device_id IN ({placeholders})
AND     update_time BETWEEN '2024-09-27 00:00:00' AND '2024-09-28 00:00:00'
ORDER BY device_id, update_time
"""

# 3.1 修改 SQL 查询以获取间隔 5 分钟的数据
query = f"""
SELECT  device_id,
        update_time AS time,
        JSON_UNQUOTE(JSON_EXTRACT(memo, '$.pa')) AS pa
FROM    (
    SELECT  device_id,
            update_time,
            JSON_UNQUOTE(JSON_EXTRACT(memo, '$.pa')) AS pa,
            ROW_NUMBER() OVER (
                PARTITION BY device_id, DATE_FORMAT(update_time, '%Y-%m-%d %H:%i')
                ORDER BY update_time
            ) AS row_num
    FROM    map_device_his
    WHERE   device_id IN ({placeholders})
    AND     update_time BETWEEN '2025-09-27 00:00:00' AND '2024-09-28 00:00:00'
) AS subquery
WHERE   row_num = 1
ORDER BY device_id, time
"""

df = pd.read_sql(query, engine)

# 4. 清洗脏数据
df['pa'] = df['pa'].astype(str).str.replace(r'(\d+)\.\d+\.', r'\1.', regex=True)
df['pa'] = pd.to_numeric(df['pa'], errors='coerce') - 100000  # 减去气压的大数值
df = df.dropna(subset=['pa'])

# 5. 聚合重复记录，取平均值
df = df.groupby(['device_id', 'time']).mean().reset_index()

# 6. 透视：行=time，列=device_id，值=pa
pivot = df.pivot(index='time', columns='device_id', values='pa')

# 7. 前向填充缺失值
pivot = pivot.ffill()

# 8. 获取所有唯一的时间点
all_times = pd.to_datetime(pivot.index).unique()

# 9. 重索引，确保所有时间点都有记录，缺失的用NaN填充
pivot = pivot.reindex(all_times)

# 10. 将pa值取整，先替换NaN为0，再转换为整数
pivot = pivot.fillna(0).astype(int)

# 11. 仅保留所有设备都有值的时间点
pivot = pivot.dropna()

# 12. 写入 Excel（index=True 保留时间列）
pivot.to_excel('pa_all_times20251009.xlsx', index=True)

print('✅ 已生成 pa_all_times.xlsx')